proc sql;
create table final as
select *
from alldata left join id
on alldata.tmc_code=id.tmc;
quit;

proc means data=final nmiss N;
run;
proc sql;
create table final2 as
select tmc_code,measurement_tstamp format=datetime19.,speed,reference_speed,travel_time_seconds,confidence,tmc,road,direction,intersection,state,county,zip	,
start_latitude,start_longitude,end_latitude,end_longitude,miles,road_order,timezone_name,type,country 
from final;
quit;
proc means data=final2 nmiss N;
run;
proc sql;
ALTER TABLE final2 ADD Car num, HGV_Bus NUM;
update final2
set car=speed+0.3, HGV_Bus=speed-5.7;
quit;
proc sql;
ALTER TABLE final ADD Car num, HGV_Bus NUM;
update final
set car=speed+0.3, HGV_Bus=speed-5.7;
quit;

Proc sql;
create table S_John_East as
select * from final where intersection in ("SR-423/S JOHN YOUNG PKY")and direction in ("EASTBOUND")and tmc_code in ("102+10710") and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;

/*proc univariate data=S_John_East noprint;
var car HGV_Bus;
cdfplot car HGV_Bus / normal;
output out=P_S_John_East pctlpts= 0 to 100 by 5 pctlpre=car HGV_Bus;
run;
*/
proc stdize data=S_John_East PctlMtd=ORD_STAT outstat=P_S_John_East
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_S_John_East
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\S_john_east1.xlsx"
replace;
run;

Proc sql;
create table S_John_west as
select * from final where intersection in ("SR-423/S JOHN YOUNG PKY")and direction in ("WESTBOUND")and tmc_code in ("102-10710") and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=S_John_west PctlMtd=ORD_STAT outstat=P_S_John_west
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_S_John_west
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\S_john_west.xlsx"
replace;
run;

Proc sql;
create table Millenia_East as
select * from final where intersection like ("MILLENIA BLVD")and direction like("EASTBOUND") and tmc_code in ("102+10709")
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=Millenia_East PctlMtd=ORD_STAT outstat=P_Millenia_East
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_Millenia_East
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\Millenia_East.xlsx"
replace;
run;

Proc sql;
create table Millenia_West as
select * from final2 WHERE intersection in ("MILLENIA BLVD")and direction in ("WESTBOUND") and tmc_code in ("102-10709") 
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=Millenia_West PctlMtd=ORD_STAT outstat=P_Millenia_West
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_Millenia_West
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\Millenia_West.xlsx"
replace;
run;
Proc sql;
create table TEXAS_AVE_East as
select * from final2 WHERE intersection in ("TEXAS AVE S")and direction in ("EASTBOUND") 
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=TEXAS_AVE_East PctlMtd=ORD_STAT outstat=P_TEXAS_AVE_East
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_TEXAS_AVE_East
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\TEXAS_AVE_East.xlsx"
replace;
run;
Proc sql;
create table TEXAS_AVE_West as
select * from final2 WHERE intersection in ("TEXAS AVE S")and direction in ("WESTBOUND") 
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=TEXAS_AVE_West PctlMtd=ORD_STAT outstat=P_TEXAS_AVE_West
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_TEXAS_AVE_West
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\TEXAS_AVE_West.xlsx"
replace;
run;
Proc sql;
create table InDr_East as
select * from final2 WHERE intersection in ("INTERNATIONAL DR")and direction in ("EASTBOUND") 
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=InDr_East PctlMtd=ORD_STAT outstat=P_InDr_East
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_InDr_East
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\InDr_East.xlsx"
replace;
run;
Proc sql;
create table InDr_West as
select * from final2 WHERE intersection in ("INTERNATIONAL DR")and direction in ("WESTBOUND") 
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=InDr_West PctlMtd=ORD_STAT outstat=P_InDr_West
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_InDr_West
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\InDr_West.xlsx"
replace;
run;
Proc sql;
create table OBT_East as
select * from final2 WHERE intersection in ("US-17/US-92/US-441/S ORANGE BLOSSOM TRL")and direction in ("EASTBOUND") and tmc_code in ("102+50990")
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=OBT_East PctlMtd=ORD_STAT outstat=P_OBT_East
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_OBT_East
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\OBT_East.xlsx"
replace;
run;
Proc sql;
create table OBT_West as
select * from final2 WHERE intersection in ("US-17/US-92/US-441/S ORANGE BLOSSOM TRL")and direction in ("WESTBOUND") and tmc_code in ("102-50990")
and (measurement_tstamp>='11OCT2017:00:00:00'dt AND measurement_tstamp<='11OCT2017:01:59:00'dt);
order by measurement_tstamp;
quit;
proc stdize data=OBT_West PctlMtd=ORD_STAT outstat=P_OBT_West
           pctlpts=0 to 100 by 5 ;
var car HGV_Bus;
run;
proc export 
data=P_OBT_West
dbms=xlsx
outfile="C:\Users\md956724\Desktop\School Zone\Oak_Ridge\OBT_West.xlsx"
replace;
run;
